package com.neusoft.neuiotms.dcam.fixed.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.neuiotms.dcam.fixed.model.enums.TimeDim;
import com.neusoft.neuiotms.dcam.fixed.service.AssetStatsService;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class AssetStatsServiceImpl implements AssetStatsService {

    @PersistenceContext
    private EntityManager entityManager;

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> commonStats(String category, String timeDimStr, Searchable searchable) {
        final TimeDim timeDim = TimeDim.valueOf(timeDimStr);
        final String sql = this.buildSql(category, timeDim, searchable);
        AssetStatsServiceImpl.log.info("query sql: {}", sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return result;
    }

    @Override
    public Workbook exportCommonStats(String category, String tmeDim, Searchable searchable) {
        final Map<String, Object> templateData = new HashMap<>();
        templateData.put("title", this.getExcelTitleName(category));
        final List<Map<String, Object>> list = this.commonStats(category, tmeDim, searchable);
        for (int i = 0; i < list.size(); i++) {
            list.get(i).put("idx", (i + 1) + "");
        }
        templateData.put("list", list);
        Long summaryCount = 0L;
        Double summaryMoney = 0.0;

        for (final Map<String, Object> it : list) {
            final BigInteger count = (BigInteger) it.get("type_count");
            summaryCount += count.longValue();
            final Double money = (Double) it.get("amount");
            final BigDecimal bdMoney = new BigDecimal(money).setScale(2, BigDecimal.ROUND_HALF_UP);
            it.put("amount", bdMoney);
            summaryMoney += money;
        }
        templateData.put("summary_count", summaryCount);
        final BigDecimal bd = new BigDecimal(summaryMoney).setScale(2, BigDecimal.ROUND_HALF_UP);
        templateData.put("summary_money", bd);

        final TemplateExportParams params = new TemplateExportParams(
                "excel/fixedasset.xlsx");
        params.setColForEach(true);
        final Workbook workbook = ExcelExportUtil.exportExcel(params, templateData);

        PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 1, 0);

        return workbook;
    }

    @Override
    public void exportCommonStatsPdf(String category, String tmeDim, Searchable searchable, OutputStream os) {
        Font font = null;
        try {
            final BaseFont fsChinese =
                    BaseFont.createFont("fonts/msyh.ttc,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
            font = new Font(fsChinese, 12, Font.NORMAL);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

        final Workbook workbook = this.exportCommonStats(category, tmeDim, searchable);
        final Sheet worksheet0 = workbook.getSheetAt(0);
        final short availableColumns = worksheet0.getRow(0).getLastCellNum();
        final Iterator<Row> rowIterator = worksheet0.iterator();

        final Document pdf = new Document();
        //        final OutputStream os = new ByteArrayOutputStream();
        try {
            PdfWriter.getInstance(pdf, os);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.open();

        final PdfPTable pdfTable = new PdfPTable(availableColumns);
        try {
            pdfTable.setWidths(new int[] {100, 350, 400, 300, 100, 240 });
        } catch (final DocumentException e1) {
        }
        PdfPCell tableCell = null;

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                tableCell = new PdfPCell(new Phrase(row.getCell(0).getStringCellValue(), font));
                tableCell.setColspan(availableColumns);
                tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
                pdfTable.addCell(tableCell);
            } else {
                final Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    final Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    default:
                        try {
                            tableCell = new PdfPCell(new Phrase(cell.getStringCellValue(), font));
                            tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                            tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);

                        } catch (final IllegalStateException illegalStateException) {
                            if (illegalStateException.getMessage()
                                    .equals("Cannot get a STRING value from a NUMERIC cell")) {
                                tableCell = new PdfPCell(new Phrase(String.valueOf(cell.getNumericCellValue())));
                            }
                        }
                        pdfTable.addCell(tableCell);
                        break;
                    }
                }
            }

        }

        try {
            pdf.add(pdfTable);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.close();
        try {
            workbook.close();
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> personalStats(Searchable searchable) {
        final String sql = this.buildPersonalSql(searchable);
        AssetStatsServiceImpl.log.info("query sql: {}", sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return result;
    }

    @Override
    public Workbook exportPersonalStats(Searchable searchable) {
        final Map<String, Object> templateData = new HashMap<>();
        templateData.put("title", "省联社机关部门个人资产报表");
        final List<Map<String, Object>> list = this.personalStats(searchable);
        for (int i = 0; i < list.size(); i++) {
            list.get(i).put("idx", (i + 1) + "");
        }
        templateData.put("list", list);
        Long summaryCount = 0L;
        Double summaryMoney = 0.0;

        for (final Map<String, Object> it : list) {
            final BigDecimal count = (BigDecimal) it.get("amount");
            summaryCount += count.longValue();
            final Double money = (Double) it.get("money");
            final BigDecimal bdMoney = new BigDecimal(money).setScale(2, BigDecimal.ROUND_HALF_UP);
            it.put("money", bdMoney);
            summaryMoney += money;
        }
        templateData.put("summary_count", summaryCount);
        final BigDecimal bd = new BigDecimal(summaryMoney).setScale(2, BigDecimal.ROUND_HALF_UP);
        templateData.put("summary_money", bd);

        final TemplateExportParams params = new TemplateExportParams(
                "excel/personalasset.xlsx");
        params.setColForEach(true);
        final Workbook workbook = ExcelExportUtil.exportExcel(params, templateData);

        PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 1, 0);

        return workbook;
    }

    @Override
    public void exportPersonalStatsPdf(Searchable searchable, OutputStream os) {
        Font font = null;
        try {
            final BaseFont fsChinese =
                    BaseFont.createFont("fonts/msyh.ttc,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
            font = new Font(fsChinese, 12, Font.NORMAL);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

        final Workbook workbook = this.exportPersonalStats(searchable);
        final Sheet worksheet0 = workbook.getSheetAt(0);
        final short availableColumns = worksheet0.getRow(0).getLastCellNum();
        final Iterator<Row> rowIterator = worksheet0.iterator();

        final Document pdf = new Document();
        try {
            PdfWriter.getInstance(pdf, os);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.open();

        final PdfPTable pdfTable = new PdfPTable(availableColumns);
        try {
            pdfTable.setWidths(new int[] {100, 350, 400, 200, 300 });
        } catch (final DocumentException e1) {
        }
        PdfPCell tableCell = null;

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                tableCell = new PdfPCell(new Phrase(row.getCell(0).getStringCellValue(), font));
                tableCell.setColspan(availableColumns);
                tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
                pdfTable.addCell(tableCell);
            } else {
                final Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    final Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    default:
                        try {
                            tableCell = new PdfPCell(new Phrase(cell.getStringCellValue(), font));
                            tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                            tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);

                        } catch (final IllegalStateException illegalStateException) {
                            if (illegalStateException.getMessage()
                                    .equals("Cannot get a STRING value from a NUMERIC cell")) {
                                tableCell = new PdfPCell(new Phrase(String.valueOf(cell.getNumericCellValue())));
                            }
                        }
                        pdfTable.addCell(tableCell);
                        break;
                    }
                }
            }

        }

        try {
            pdf.add(pdfTable);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.close();
        try {
            workbook.close();
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> summaryStats() {
        final String sql = this.buildSummarySql();
        AssetStatsServiceImpl.log.info("query sql: {}", sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return result;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> inventoryStats(Searchable searchable) {
        final String sql = this.buildInventorySql(searchable);
        AssetStatsServiceImpl.log.info("query sql: {}", sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return result;
    }

    @Override
    public Workbook exportInventoryStats(Searchable searchable) {
        final Map<String, Object> templateData = new HashMap<>();
        templateData.put("title", "省联社机关部门资产盘点报表");
        final List<Map<String, Object>> list = this.inventoryStats(searchable);
        for (int i = 0; i < list.size(); i++) {
            list.get(i).put("idx", (i + 1) + "");
        }

        templateData.put("list", list);
        Long summaryAssetCount = 0L;
        Long summaryWaitCount = 0L;
        Long summaryEqualityCount = 0L;
        Long summaryLossCount = 0L;
        Long summaryGainCount = 0L;

        for (final Map<String, Object> it : list) {
            final BigInteger assetCount = (BigInteger) it.get("asset_count");
            summaryAssetCount += assetCount.longValue();

            final BigInteger waitCount = (BigInteger) it.get("wait_count");
            summaryWaitCount += waitCount.longValue();

            final BigInteger equalityCount = (BigInteger) it.get("equality_count");
            summaryEqualityCount += equalityCount.longValue();

            final BigInteger lossCount = (BigInteger) it.get("loss_count");
            summaryLossCount += lossCount.longValue();

            final BigInteger gainCount = (BigInteger) it.get("gain_count");
            summaryGainCount += gainCount.longValue();
        }
        templateData.put("summary_asset_count", summaryAssetCount);
        templateData.put("summary_wait_count", summaryWaitCount);
        templateData.put("summary_equality_count", summaryEqualityCount);
        templateData.put("summary_loss_count", summaryLossCount);
        templateData.put("summary_gain_count", summaryGainCount);

        final TemplateExportParams params = new TemplateExportParams(
                "excel/inventoryasset.xlsx");
        params.setColForEach(true);
        final Workbook workbook = ExcelExportUtil.exportExcel(params, templateData);

        //        PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 1, 0);

        return workbook;
    }

    @Override
    public void exportInventoryStatsPdf(Searchable searchable, OutputStream os) {
        Font font = null;
        try {
            final BaseFont fsChinese =
                    BaseFont.createFont("fonts/msyh.ttc,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
            font = new Font(fsChinese, 12, Font.NORMAL);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

        final Workbook workbook = this.exportInventoryStats(searchable);
        final Sheet worksheet0 = workbook.getSheetAt(0);
        final short availableColumns = worksheet0.getRow(0).getLastCellNum();
        final Iterator<Row> rowIterator = worksheet0.iterator();

        final Document pdf = new Document();
        try {
            PdfWriter.getInstance(pdf, os);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.open();

        final PdfPTable pdfTable = new PdfPTable(availableColumns);
        //        try {
        //            pdfTable.setWidths(new int[] {350, 400, 200, 300 });
        //        } catch (final DocumentException e1) {
        //        }
        PdfPCell tableCell = null;

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                tableCell = new PdfPCell(new Phrase(row.getCell(0).getStringCellValue(), font));
                tableCell.setColspan(availableColumns);
                tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
                pdfTable.addCell(tableCell);
            } else if (row.getRowNum() == 1) {
                final Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    final Cell cell = cellIterator.next();
                    final int columnIdx = cell.getColumnIndex();
                    try {
                        tableCell = new PdfPCell(new Phrase(cell.getStringCellValue(), font));
                        tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                        tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
                    } catch (final IllegalStateException illegalStateException) {
                        if (illegalStateException.getMessage()
                                .equals("Cannot get a STRING value from a NUMERIC cell")) {
                            tableCell = new PdfPCell(new Phrase(String.valueOf(cell.getNumericCellValue())));
                        }
                    }
                    if ((columnIdx == 0) || (columnIdx == 1) || (columnIdx == 2) || (columnIdx == 3)
                            || (columnIdx == 4) || (columnIdx == 5)) {
                        tableCell.setRowspan(2);
                        pdfTable.addCell(tableCell);
                    } else if (columnIdx == 6) {
                        tableCell.setColspan(3);
                        pdfTable.addCell(tableCell);
                    }
                }
            } else if (row.getRowNum() == 2) {
                final Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    final Cell cell = cellIterator.next();
                    final int columnIdx = cell.getColumnIndex();
                    if ((columnIdx == 6) || (columnIdx == 7) || (columnIdx == 8)) {
                        try {
                            tableCell = new PdfPCell(new Phrase(cell.getStringCellValue(), font));
                            tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                            tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
                        } catch (final IllegalStateException illegalStateException) {
                            if (illegalStateException.getMessage()
                                    .equals("Cannot get a STRING value from a NUMERIC cell")) {
                                tableCell = new PdfPCell(new Phrase(String.valueOf(cell.getNumericCellValue())));
                            }
                        }
                        pdfTable.addCell(tableCell);
                    }
                }
            } else {
                final Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    final Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    default:
                        try {
                            tableCell = new PdfPCell(new Phrase(cell.getStringCellValue(), font));
                            tableCell.setHorizontalAlignment(Element.ALIGN_CENTER);
                            tableCell.setVerticalAlignment(Element.ALIGN_MIDDLE);

                        } catch (final IllegalStateException illegalStateException) {
                            if (illegalStateException.getMessage()
                                    .equals("Cannot get a STRING value from a NUMERIC cell")) {
                                tableCell = new PdfPCell(new Phrase(String.valueOf(cell.getNumericCellValue())));
                            }
                        }
                        pdfTable.addCell(tableCell);
                        break;
                    }
                }
            }

        }

        try {
            pdf.add(pdfTable);
        } catch (final DocumentException e) {
            AssetStatsServiceImpl.log.error("", e);
        }
        pdf.close();
        try {
            workbook.close();
        } catch (final IOException e) {
            AssetStatsServiceImpl.log.error("", e);
        }

    }

    private String buildSql(String category, TimeDim timeDim, Searchable searchable) {

        String dateFormatStr = "date_format(purchase_date, '%Y-%m-%d')";
        switch (timeDim) {
        case day:
            dateFormatStr = "date_format(purchase_date, '%Y-%m-%d')";
            break;
        case year:
            dateFormatStr = "YEAR (purchase_date)";
            break;
        case halfyear:
            dateFormatStr = "CONCAT(year(purchase_date), '-', floor(DATE_FORMAT(purchase_date,'%m')/7))";
            break;
        case month:
            dateFormatStr = "date_format(purchase_date, '%Y-%m')";
            break;
        }

        final StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" " + dateFormatStr);
        sb.append(" AS date,");
        sb.append(" management_org_name AS depart_name,");
        sb.append(" small_type_name AS type_name,");
        sb.append(" count(*) AS type_count,");
        sb.append(" sum(money) AS amount");
        sb.append(" FROM");
        sb.append(" neuiot_fixed_asset");
        sb.append(" WHERE");
        sb.append(" belongs = 'sl'");
        sb.append(" AND categories = '" + category + "'");
        if (searchable.hasKey("date")) {
            sb.append(" AND " + dateFormatStr + " = '" + searchable.getStrValue("date") + "'");
        }
        sb.append(" GROUP BY");
        sb.append(" " + dateFormatStr + ",");
        sb.append(" management_org_code,");
        sb.append(" management_org_name,");
        sb.append(" small_type_code,");
        sb.append(" small_type_name");
        sb.append(" ORDER BY");
        sb.append(" date desc");

        return sb.toString();
    }

    private String buildPersonalSql(Searchable searchable) {
        final StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append("     management_user_name as user,");
        sb.append("     small_type_name as type,");
        sb.append("     sum(amount) as amount,");
        sb.append("     sum(money) as money");
        sb.append(" FROM");
        sb.append(" (");
        sb.append("     SELECT");
        sb.append("         IFNULL(management_user_code, '') AS management_user_code,");
        sb.append("         IFNULL(management_user_name, '') AS management_user_name,");
        sb.append("         amount,");
        sb.append("         money,");
        sb.append("         belongs,");
        sb.append("         small_type_code,");
        sb.append("         small_type_name");
        sb.append("     FROM");
        sb.append("         neuiot_fixed_asset");
        sb.append(" ) AS t");
        sb.append(" WHERE");
        sb.append("     belongs = 'sl'");
        if (searchable.hasKey("user")) {
            sb.append(" AND management_user_name like '%" + searchable.getStrValue("user") + "%'");
        }
        sb.append(" GROUP BY");
        sb.append("     management_user_code,");
        sb.append("     management_user_name,");
        sb.append("     small_type_name");
        sb.append(" order by management_user_name;");

        return sb.toString();
    }

    private String buildSummarySql() {
        final StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append("     categories,");
        sb.append("     sum(amount) AS amount,");
        sb.append("     sum(money) AS money");
        sb.append(" FROM");
        sb.append("     neuiot_fixed_asset");
        sb.append(" WHERE");
        sb.append("     belongs = 'sl'");
        sb.append(" GROUP BY");
        sb.append("     categories");
        return sb.toString();
    }

    private String buildInventorySql(Searchable searchable) {
        final StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append("     a.id AS duty_id,");
        sb.append("     date_format(a.create_time, '%Y-%m-%d') AS start_time,");
        sb.append("     date_format(a.update_time, '%Y-%m-%d') AS finish_time,");
        sb.append("     a.user_name AS principal,");
        sb.append("     a. NAME AS department_name,");
        sb.append("     c.*");
        sb.append(" FROM");
        sb.append("     neuiot_inventory_duty a");
        sb.append(" LEFT JOIN neuiot_inventory_plan b ON a.inventory_plan_id = b.id");
        sb.append(" LEFT JOIN (");
        sb.append("     SELECT");
        sb.append("         a.inventory_duty_id,");
        sb.append("         count(1) AS asset_count,");
        sb.append("         COUNT(");
        sb.append("             CASE");
        sb.append("             WHEN duty_result_status = 'wait' THEN");
        sb.append("                 1");
        sb.append("             ELSE");
        sb.append("                 NULL");
        sb.append("             END");
        sb.append("         ) AS 'wait_count',");
        sb.append("         COUNT(");
        sb.append("             CASE");
        sb.append("             WHEN duty_result_status = 'loss' THEN");
        sb.append("                 1");
        sb.append("             ELSE");
        sb.append("                 NULL");
        sb.append("             END");
        sb.append("         ) AS 'loss_count',");
        sb.append("         COUNT(");
        sb.append("             CASE");
        sb.append("             WHEN duty_result_status = 'gain' THEN");
        sb.append("                 1");
        sb.append("             ELSE");
        sb.append("                 NULL");
        sb.append("             END");
        sb.append("         ) AS 'gain_count',");
        sb.append("         COUNT(");
        sb.append("             CASE");
        sb.append("             WHEN duty_result_status = 'equality' THEN");
        sb.append("                 1");
        sb.append("             ELSE");
        sb.append("                 NULL");
        sb.append("             END");
        sb.append("         ) AS 'equality_count'");
        sb.append("     FROM");
        sb.append("         neuiot_inventory_result a");
        sb.append("     LEFT JOIN neuiot_inventory_duty b ON a.inventory_duty_id = b.id");
        sb.append("     LEFT JOIN neuiot_inventory_plan c ON b.inventory_plan_id = c.id");
        sb.append("     WHERE");
        sb.append("         b.`status` = 'finish'");
        sb.append("     AND c.belongs = 'sl'");
        sb.append("     GROUP BY");
        sb.append("         a.inventory_duty_id");
        sb.append(" ) c ON a.id = c.inventory_duty_id");
        sb.append(" WHERE");
        sb.append("     b.belongs = 'sl'");
        sb.append(" AND a. STATUS = 'finish'");
        if (searchable.hasKey("dateRangeFrom")) {
            sb.append(
                    " AND date_format(a.create_time, '%Y-%m-%d') >= '" + searchable.getStrValue("dateRangeFrom") + "'");
        }
        if (searchable.hasKey("dateRangeTo")) {
            sb.append(
                    " AND date_format(a.create_time, '%Y-%m-%d') <= '" + searchable.getStrValue("dateRangeTo") + "'");
        }
        sb.append(" order by a.create_time desc");
        return sb.toString();
    }

    private String getExcelTitleName(String category) {
        if ("1".equals(category)) {
            return "省联社机关部门固定资产报表";
        } else if ("2".equals(category)) {
            return "省联社机关部门低值易耗品报表";
        } else if ("3".equals(category)) {
            return "省联社机关部门消耗品报表";
        }
        return "";
    }

}
